﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Pr0ject0.Models
{
    public class AvailableHoursDB
    {
        private string connection;
        private List<AvailableHoursModel> available;
        public List<AvailableHoursModel> AvailableHoursList { get { return new List<AvailableHoursModel>(available); } }

        public AvailableHoursDB()
        {
            connection = "DB";
            update();
        }

        public AvailableHoursDB(string connectionString)
        {
            connection = connectionString;
            update();
        }

        public void update()
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
            SqlCommand cmd = new SqlCommand("SELECT * FROM [AvailableHours]", conn);
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            available = new List<AvailableHoursModel>();

            while (reader.Read())
            {
                AvailableHoursModel model = new AvailableHoursModel();
                model.ID = Convert.ToInt32(reader["Id"].ToString());
                model.monStart = TimeSpan.Parse(reader["monStart"].ToString());
                model.monEnd = TimeSpan.Parse(reader["monEnd"].ToString());
                model.tueStart = TimeSpan.Parse(reader["tueStart"].ToString());
                model.tueEnd = TimeSpan.Parse(reader["tueEnd"].ToString());
                model.wedStart = TimeSpan.Parse(reader["wedStart"].ToString());
                model.wedEnd = TimeSpan.Parse(reader["wedEnd"].ToString());
                model.thurStart = TimeSpan.Parse(reader["thurStart"].ToString());
                model.thurEnd = TimeSpan.Parse(reader["thurEnd"].ToString());
                model.friStart = TimeSpan.Parse(reader["friStart"].ToString());
                model.friEnd = TimeSpan.Parse(reader["friEnd"].ToString());
                model.satStart = TimeSpan.Parse(reader["satStart"].ToString());
                model.sunStart = TimeSpan.Parse(reader["sunStart"].ToString());
                model.sunEnd = TimeSpan.Parse(reader["sunEnd"].ToString());
                model.desiresFullTime = Convert.ToBoolean(reader["desiresFullTime"].ToString());
                available.Add(model);
            }

            reader.Close();
            conn.Close();
        }

        public void Add(List<AvailableHoursModel> list)
        {
            foreach (AvailableHoursModel model in list)
            {
                Add(model);
            }
        }

        public void Add(AvailableHoursModel model)
        {
            string statement ="INSERT INTO AvailableHours VALUES({0}, '{1}', '{2}', '{3}', '{4}', " + 
                "'{5}', '{6}', '{7}', '{8}','{9}', '{10}', '{11}', '{12}', '{13}', '{14}', {15})";
            string insert;
            Object[] objects = { model.ID, model.monStart, model.monEnd, model.tueStart, model.tueEnd, model.wedStart, 
                               model.wedEnd, model.thurStart, model.thurEnd, model.friStart, model.friEnd, model.satStart,
                               model.satEnd, model.sunStart, model.sunEnd, Convert.ToInt32(model.desiresFullTime)};

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
            insert = string.Format(statement, objects);
            SqlCommand cmd = new SqlCommand(insert, conn);

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

            update();
        }
    }
}